Imports System.Security
Imports GenHelper
Imports System.Data.Common
Imports MySql.Data.MySqlClient
Imports System.Collections
Imports System.Configuration
Imports System.Security.Principal
Imports System.Net.Mail
Imports System.Threading

Partial Class UserLogin
    Inherits System.Web.UI.Page


    Function CheckValidity(ByVal UName As String, ByVal UPass As String) As Boolean

        Dim Sql As String
        Dim Record As DbDataReader
        Dim Params(1) As GenParameter
        Dim Cookie As HttpCookie = New HttpCookie("Profile")
        Dim rolelist As New ArrayList
        Dim RoleRecords As DbDataReader
        Dim rolelistarray As String()

        Response.Cookies.Add(Cookie)


        Sql = "Select * from ExecutiveMaster INNER JOIN DepartmentMaster ON ExecutiveMaster.DepartmentID = DepartmentMaster.DepartmentID" & _
        " WHERE ExecutiveUserName = ?EName AND UserPassword = sha1(?EPass) AND Active = 1"

        Params(0) = New GenParameter("?EName", UName)
        Params(1) = New GenParameter("?EPass", UPass)

        Record = GenHelper.ExecuteReader(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Record.HasRows Then

            If Record.Read() Then

                'Check if the Person in Active

                If Record.Item("Locked") = 1 Then
                    Login1.FailureText = "Your Account has been disabled. Please contact the MD for Permission"
                    Return False
                End If

                'Get all the information that we may need later

                Cookie.Values.Add("ExecutiveID", Record.Item("ExecutiveID"))
                Cookie.Values.Add("ExecutiveName", Record.Item("ExecutiveName"))
                Cookie.Values.Add("ExecutiveUserName", Record.Item("ExecutiveUserName"))
                Cookie.Values.Add("Designation", Record.Item("Designation"))
                Cookie.Values.Add("Location", Record.Item("Location"))
                Cookie.Values.Add("AccessID", Record.Item("AccessID"))
                Cookie.Values.Add("DepartmentName", Record.Item("DepartmentName"))


                'Profile.ExecutiveID = Record.Item("ExecutiveID")
                'Profile.Name = Record.Item("ExecutiveName")
                'Profile.Designation = Record.Item("Designation")
                'Profile.Location = Record.Item("Location")
                'Profile.AccessID = Record.Item("AccessID")
                'Profile.DepartmentID = Record.Item("DepartmentID")

                'Set the variable if he's using his default password
                If (UPass = "test123") Then
                    Cookie.Values.Add("TestPass", True)
                Else
                    Cookie.Values.Add("TestPass", False)
                End If

                '' To Assign the Roles

                ReDim Params(0)

                Sql = "Select DepartmentMaster.DepartmentName FROM RoleMaster INNER JOIN DepartmentMaster" & _
                " ON RoleMaster.DepartmentID = DepartmentMaster.DepartmentID" & _
                " WHERE RoleMaster.ExecutiveID = ?ExecutiveID"

                Params(0) = New GenParameter("?ExecutiveID", Record.Item("ExecutiveID"), Data.DbType.UInt32)
                RoleRecords = GenHelper.ExecuteReader(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

                Do While RoleRecords.Read()
                    rolelist.Add(RoleRecords("DepartmentName"))
                Loop
                RoleRecords.Close()

                'Convert rolelist into Array String
                rolelistarray = rolelist.ToArray(GetType(String))

                ''Store the Information about the roles
                'HttpContext.Current.User = New GenericPrincipal(User.Identity, rolelistarray)

                Cache.Insert(Record.Item("ExecutiveID").ToString, rolelistarray)
                rolelistarray = Cache(Record.Item("ExecutiveID").ToString)
            End If

            Record.Close()

            Return True

        Else

            'There are no rows to return
            Login1.FailureText = "Your login attempt was not successful. Please try again."
            Return False

        End If

    End Function


    Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate

        Dim Authenticated As Boolean
        Dim roles As String


        Authenticated = CheckValidity(Login1.UserName, Login1.Password)
        roles = Request.Cookies("Profile").Item("DepartmentName")


        If Authenticated = True Then

            e.Authenticated = Authenticated
            FormsAuthentication.RedirectFromLoginPage(Login1.UserName, False)
        Else
            e.Authenticated = False
        End If



    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Make sure that the person is signed out
        FormsAuthentication.SignOut()
        Page.Title = "Login Page"
        Login1.Focus()

        Dim Sql As String
        Dim Value As String
        Dim Params(0) As GenParameter

        Sql = "Select LastDateSent FROM ReportSent LIMIT 1"
        Value = GenHelper.ExecuteScalar(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString()).ToString
        Dim RightNow As Date

        RightNow = Now.AddHours(10)
        RightNow = RightNow.AddMinutes(30)


        ' Check if the defaulters report Mails were sent for the day

        If DateDiff(DateInterval.Day, Date.Parse(Value), RightNow) <> 0 And RightNow.DayOfWeek <> DayOfWeek.Monday Then

            Dim TDate As String
            TDate = RightNow.Year & "/" & RightNow.Month & "/" & RightNow.Day

            'Insert the new date on which mails were sent

            Sql = "Update ReportSent SET LastDateSent = ?TDate"
            Params(0) = New GenParameter("?TDate", TDate, Data.DbType.Date)
            GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf SendReports), 1)
        End If

    End Sub

    Protected Sub SendReports(ByVal s As Object)

        Dim ManagerSql As String
        Dim ExecutiveSql As String
        Dim ManagerReader As DbDataReader
        Dim ExecutiveReader As DbDataReader
        Dim Message As String
        Dim RightNow As Date
        Dim TDate As String

        RightNow = Now.AddHours(10)
        RightNow = RightNow.AddMinutes(30)
        TDate = RightNow.Year & "/" & RightNow.Month & "/" & RightNow.Day

        RightNow = RightNow.AddDays(-1)

        Dim Params(0) As GenParameter

        ManagerSql = "SELECT e.SupervisorID, Ex.ExecutiveName, Ex.email FROM executivemaster e INNER JOIN ExecutiveMaster ex ON e.SupervisorID = ex.ExecutiveID" & _
                    " WHERE e.ExecutiveID NOT IN (Select ExecutiveID FROM ReportMaster WHERE ReportDate = cast(((now() + interval _utf8'10:30' hour_minute) - interval 1 day) as date)) AND e.DepartmentID <> 2 and e.active = 1 AND e.departmentid <> 6" & _
                    " UNION" & _
                    " SELECT e.SupervisorID, Ex.ExecutiveName, Ex.email FROM executivemaster e INNER JOIN ExecutiveMaster ex ON e.SupervisorID = ex.ExecutiveID" & _
                    " WHERE e.ExecutiveID NOT IN (Select Distinct ExecutiveID from WorkflowMaster WHERE MeetingDate = cast(((now() + interval _utf8'10:30' hour_minute) - interval 1 day) as date)) AND e.DepartmentID = 2 and e.active = 1"

        ExecutiveSql = "(SELECT ExecutiveName, 'Not Submitted Management Report' AS 'Status' FROM executivemaster e" & _
                    " WHERE ExecutiveID NOT IN (Select ExecutiveID FROM ReportMaster WHERE ReportDate = cast(((now() + interval _utf8'10:30' hour_minute) - interval 1 day) as date)) AND e.DepartmentID <> 2 and e.active = 1 AND e.departmentid <> 6 AND e.SupervisorID = ?SupervisorID)" & _
                    " UNION" & _
                    " (SELECT ExecutiveName, 'Not Entered Marketing Report in Grond' AS Status FROM executivemaster e" & _
                    " WHERE ExecutiveID NOT IN (Select Distinct ExecutiveID from WorkflowMaster WHERE MeetingDate = cast(((now() + interval _utf8'10:30' hour_minute) - interval 1 day) as date)) AND e.DepartmentID = 2 and e.active = 1 AND e.SupervisorID = ?SupervisorID)" & _
                    " ORDER BY ExecutiveName"

        'Get the List of Managers who have to be informed

        ManagerReader = GenHelper.ExecuteReader(ManagerSql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        While (ManagerReader.Read)

            Params(0) = New GenParameter("?SupervisorID", ManagerReader.Item("SupervisorID"), Data.DbType.Int16)
            ExecutiveReader = GenHelper.ExecuteReader(ExecutiveSql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            'Create the Message

            Message = "Dear " & ManagerReader.Item("ExecutiveName") & ",<p><p>Greetings from GROND! This is to inform you that the following members of your" & _
                " team have not submitted their reports yesterday(" & RightNow.DayOfWeek.ToString & "):<p><p>"

            'For Each Manager, append the list of executives

            While ExecutiveReader.Read
                Message = Message & ExecutiveReader.Item("ExecutiveName") & " (" & ExecutiveReader.Item("Status") & ")<p>"
            End While

            Message = Message & "<p>Yours Sincerely<p><p>GROND"

            Dim Mail As New MailMessage()

            Mail.From = New MailAddress("GROND@zakgroup.com")
            Mail.To.Add(ManagerReader.Item("email"))
            Mail.CC.Add("mktg@zakgroup.com")
            Mail.To.Add("bhagwad@zakgroup.com")
            Mail.Subject = ManagerReader.Item("ExecutiveName") & "-Defaulters in your team"
            Mail.Body = Message
            Mail.IsBodyHtml = True

            Dim SMTP As New SmtpClient("127.0.0.1")
            SMTP.Send(Mail)

        End While

        

    End Sub
End Class
